﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Rison.DataAccess.Common;
using Rison.DBHelper;

namespace Rison.DataAccess.Data.Biz
{
    public sealed class CPResultDetailDb : DataAccessBase
    {
        public CPResultDetailDb()
            : base()
        {
        }

        public CPResultDetailDb(DbBaseHelper dbTransaction)
            : base(dbTransaction)
        {
        }
        
        public CPResultDetailDb(string tempDbConnStr)
            : base()
        {
            //设置第三方数据库的连接串
            base.SetTempConnectionString(tempDbConnStr);
        }

        public CPResultDetailDb(string tempDbConnStr, DbBaseHelper dbTransaction)
            : base(dbTransaction)
        {
            base.SetTempConnectionString(tempDbConnStr);
        }

        protected override string tableName
        {
            get { return "Biz_CPResultDetail"; }
        }

        protected override string IdentityFieldName
        {
            get { return "RDID"; }
        }


        public DataSet GetDepartments(string condition)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Mailbox.ID as bmid,mailboxdisplay as bmmc, Category.CategoryName as bmfl from Mailbox ");
            sql.Append("inner join CategoryMailboxRel on Mailbox.ID = CategoryMailboxRel.MailBOxID ");
            sql.Append("inner join Category on CategoryMailboxRel.CategoryID = Category.CategoryID ");
            sql.Append("where (Category.CategoryID= 'd488d222-54c6-4276-a646-0850d909aef4' ");
            sql.Append("or Category.parentCategoryID='d488d222-54c6-4276-a646-0850d909aef4') ");

            if (!string.IsNullOrEmpty(condition))
                sql.AppendFormat("and {0} ", condition);

            //sql.Append("order by Category.CategoryName,mailboxdisplay");
            //按部门类型ordernum,和部门 ordernum 排序
            sql.Append("order by Category.OrderNum,CategoryMailboxRel.OrderNum");

            return base.ExecuteSql(sql.ToString(), "departments");
        }

        public string GetDepartmentCategoryRelId(string bmid)
        {
            DataRow row = base.ExecuteSqlToDataRow("select id from CategoryMailboxRel where MailBoxID='" + bmid + "'");
            if (row != null)
                return row[0].ToString();
            return string.Empty;
        }

        public int SaveResult(string sqlString)
        {
            return base.ExecuteNonSql(sqlString);
        }

        public bool PasswordExists(string password)
        {
            DataRow row = base.ExecuteSqlToDataRow("select 1 from Biz_CPResultDetail where password='" + password + "'");

            return !(row == null);            
        }

        public int GetSXH()
        {
            DataRow row = ExecuteSqlToDataRow("select COUNT(1) from request where CAST(convert(varchar(10),GETDATE(),120) as datetime)=CAST(convert(varchar(10),SubmitTime,120) as datetime) ");
            if (row != null)
            {
                return Convert.ToInt32(row[0]) + 1;
            }
            return 1;
        }

        /// <summary>
        /// verfiy password
        /// </summary>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool PasswordExistsNew(string password)
        {
            DataRow row = base.ExecuteSqlToDataRow("select Password  from EvaluationUser where Password='" + password + "'");

            return !(row == null);
        }

        public DataSet GetWSCPbyPwd(string password)
        {
            DataSet ds = base.ExecuteSql("select * from Request where RequestType = '网上测评' and EvaluationUserPassword='" + password + "'", "GetWSCPbyPwd");
            return ds;
        }


        public DataRow GetWSCPbydepart(string password,string departid)
        {
            DataRow dr = base.ExecuteSqlToDataRow("select * from Request where RequestType = '网上测评' and EvaluationUserPassword='" + password + "' and MailBoxID='" + departid  + "'");
            return dr;
        }
    }
}